#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
**=============================================================
 * Copyright: 2012~2015 NSN
 * FullName: utils.dict_tosql
 * Description: 
 * Changes: 
 *==============================================================
 * Date: Apr 18, 2014
 * Author: y21chen(yong-cy.chen@nsn.com)          
 * Comment:  Create file, implement feature    
**==============================================================
"""

class DictSqlConvert(object):
    def __init__(self,data):
        '''
            data is a dict which must have a table value
            {tables:'test test1','insert|update|query|delete':{'keys_or_values':'','dest_or_condition':''}}
        '''
        self.data = data
        
    def dict_to_kvstr(self):
        '''
            Convert the dict to keys string and values string
            a,b,c,d '"va","vb","vc","vd"'
        '''
        ks = ','.join(self.data.keys())
        vs = '"'+'","'.join(self.data.values())+'"'
        return ks,vs
    
    def dict_to_keqv(self):
        '''
            Convert the dict to key="value" string
            such as 'a="va",b="vb"'
        '''
        kvs = ''
        for k,v in self.data.items():
            if kvs == '':
                kvs = k+'="'+v+'"'
            else:
                kvs = kvs+','+k+'="'+v+'"' 
        return kvs
    
            
    def to_insert(self):
        ins = ''
        if self.data.get('insert')  :
            ins = 'insert into '+self.data['tables']+' ('+self.data['insert']['keys_or_values']\
            +') values ('+self.data['insert']['dest_or_condition']+')'
        return ins
    def to_query(self):
        '''
            Convert the dict to sql string
        '''
        qs = ''
        if self.data.get('query'):
            if self.data['query']['keys_or_values']:
                qs = 'select '+self.data['query']['keys_or_values']+' from '+self.data['tables']\
                +' where '+self.data['query']['dest_or_condition']
            else:
                qs = 'select * from '+self.data['tables']+' where '+self.data['query']['dest_or_condition']
        return qs
    def to_update(self):
        us = ''
        if self.data.get('update'):
            us = 'update '+self.data['tables']+' set '+self.data['update']['keys_or_values']\
            +' where ' +self.data['update']['dest_or_condition']
        return us
    def to_delete(self):
        ds = ''
        if self.data.get('delete'):
            ds = 'delete '+self.data['delete']['keys_or_values']+' from '+self.data['tables']\
            +' where '+self.data['delete']['dest_or_condition']
        return ds
    
class SimpleTBDictToSql(object):
    '''
        Simple table dict to string
        init with table name
        you can convert you values dict to sql string
    '''
    def __init__(self,table):
        self.table = table
        
    def get_query(self,condition):
        data = {}
        data['tables'] = self.table
        data['query'] = {}
        data['query']['keys_or_values'] = '*'
        data['query']['dest_or_condition'] = condition
        return DictSqlConvert(data).to_query()
    
    def get_insert_one(self,values):
        data = {}
        data['tables'] = self.table
        data['insert'] = {}
        ks,vs = DictSqlConvert(values).dict_to_kvstr()
        data['insert']['keys_or_values'] = ks
        data['insert']['dest_or_condition'] = vs
        return DictSqlConvert(data).to_insert()
    
    def get_update(self,values,condition):
        data = {}
        data['tables'] = self.table
        data['update'] = {}
        kvs = DictSqlConvert(values).dict_to_keqv()
        data['update']['keys_or_values'] = kvs
        data['update']['dest_or_condition'] = condition
        return DictSqlConvert(data).to_update()
    
    def get_delete(self,condition):
        data = {}
        data['tables'] = self.table
        data['delete'] = {}
        data['delete']['keys_or_values'] = ''
        data['delete']['dest_or_condition'] = condition
        return DictSqlConvert(data).to_delete()
       
if __name__ == "__main__":
    data = {}
    data['tables'] = "test_table"
    
    data['insert'] = {}
    data['insert']['keys_or_values'] = "a,b,c,d"
    data['insert']['dest_or_condition'] = '"1","2","3","4"'
    
    data['query'] = {}
    data['query']['keys_or_values'] = ''
    data['query']['dest_or_condition'] = 'a="5"'
 
    data['update'] = {}
    data['update']['keys_or_values'] = 'a="5",b="6",c="7",d="8"'
    data['update']['dest_or_condition'] = 'a="5"'
     
    data['delete'] = {}
    data['delete']['keys_or_values'] = ''
    data['delete']['dest_or_condition'] = 'a="5"'
    
    print(data)
    
    conv = DictSqlConvert(data)
    print(conv.to_insert())
    print(conv.to_query())
    print(conv.to_update())
    print(conv.to_delete())
        
        
        
        
